package com.partsoft.dits.utils;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;

import org.apache.commons.io.IOUtils;
import org.springframework.jdbc.core.SqlParameterValue;
import org.springframework.jdbc.core.support.SqlLobValue;
import org.springframework.util.Assert;
import org.springframework.util.ClassUtils;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import com.partsoft.utils.HexUtils;

public class SQLTypeUtils {

	public static String getXMLEncodingStringValue(ResultSet rs, String columnName, int columnSQLType) {
		String retstr = getStringValue(rs, columnName, columnSQLType).trim();
		retstr = StringUtils.replace(retstr, "<", "&lt;");
		retstr = StringUtils.replace(retstr, ">", "&gt;");
		retstr = StringUtils.replace(retstr, "&", "&amp;");
		retstr = StringUtils.replace(retstr, "'", "&apos;");
		retstr = StringUtils.replace(retstr, "\"", "&quot;");
		return retstr;
	}

	public static String getSampleValue(String columnName, int columnSQLType) {
		String value = "";
		switch (columnSQLType) {
		case Types.BIT:
		case Types.TINYINT:
		case Types.SMALLINT:
		case Types.INTEGER:
		case Types.BIGINT:
			value = "1";
			break;
		case Types.FLOAT:
		case Types.REAL:
			value = new Float(0.0).toString();
			break;
		case Types.NUMERIC:
		case Types.DECIMAL:
			value = new BigDecimal(0.0).toString();
			break;
		case Types.DOUBLE:
			value = new Double(0.0).toString();
			break;
		case Types.CHAR:
		case Types.VARCHAR:
		case Types.LONGVARCHAR:
			value = columnName;
			break;
		case Types.DATE:
			Date dt = new Date();
			if (dt != null)
				value = CalendarUtils.toXmlDate(dt);
			break;
		case Types.TIMESTAMP:
			Date tsm = new Date();
			if (tsm != null)
				value = CalendarUtils.toXmlDateTime(tsm);
			break;
		case Types.TIME:
			Date tm = new Date();
			if (tm != null)
				value = CalendarUtils.toXmlTime(tm);
			break;
		case Types.BOOLEAN:
			value = new Boolean(true).toString();
			break;
		case Types.CLOB:
			break;
		}
		return value;
	}

	public static String getStringValue(ResultSet rs, String columnName, int columnSQLType) {
		String value = "";
		try {
			switch (columnSQLType) {
			case Types.BIT:
				Boolean vb = rs.getBoolean(columnName);
				if (vb != null)
					value = vb.toString();
				break;
			case Types.TINYINT:
				Byte bt = rs.getByte(columnName);
				if (bt != null)
					value = bt.toString();
				break;
			case Types.SMALLINT:
				Short st = rs.getShort(columnName);
				if (st != null)
					value = st.toString();
				break;
			case Types.INTEGER:
				Integer it = rs.getInt(columnName);
				if (it != null)
					value = it.toString();
				break;
			case Types.BIGINT:
				Long lg = rs.getLong(columnName);
				if (lg != null)
					value = lg.toString();
				break;
			case Types.FLOAT:
			case Types.REAL:
				Float ft = rs.getFloat(columnName);
				if (ft != null)
					value = ft.toString();
				break;
			case Types.NUMERIC:
			case Types.DECIMAL:
				BigDecimal bd = rs.getBigDecimal(columnName);
				if (bd != null)
					value = bd.toString();
				break;
			case Types.DOUBLE:
				Double db = rs.getDouble(columnName);
				if (db != null)
					value = db.toString();
				break;
			case Types.CHAR:
			case Types.VARCHAR:
			case Types.LONGVARCHAR:
				value = rs.getString(columnName);
				if (!StringUtils.hasText(value))
					value = "";
				break;
			case Types.DATE:
				Date dt = rs.getDate(columnName);
				if (dt != null)
					value = CalendarUtils.toXmlDate(dt);
				break;
			case Types.TIMESTAMP:
				Timestamp tsm = rs.getTimestamp(columnName);
				if (tsm != null)
					value = CalendarUtils.toXmlDateTime(tsm);
				break;
			case Types.TIME:
				Time tm = rs.getTime(columnName);
				if (tm != null)
					value = CalendarUtils.toXmlTime(tm);
				break;
			case Types.BOOLEAN:
				Boolean bl = rs.getBoolean(columnName);
				if (bl != null)
					value = bl.toString();
				break;
			case Types.BLOB:
			case Types.BINARY:
			case Types.VARBINARY:
			case Types.LONGVARBINARY:
			case Types.ARRAY:
			case Types.REF:
			case Types.DATALINK:
				InputStream its = rs.getAsciiStream(columnName);
				if (its != null) {
					byte []valueByte = IOUtils.toByteArray(its);
					try {
						value = new String(valueByte);
						if (!HexUtils.isHex(value)) {
							value = HexUtils.toHex(valueByte);
						}
					} catch (Throwable e) {
						throw new IllegalStateException(e.getMessage(), e);
					} finally {
						if (its != null) {
							try {
								its.close();
							} catch (IOException e) {
							}
						}
					}
				}
				break;
			}
		} catch (Throwable e) {
			throw new IllegalStateException(e.getMessage(), e);
		}
		return value;
	}

	public static Object toSqlParameterValue(Object v, int fieldType) {
		if (v instanceof SqlParameterValue)
			return v;
		return new SqlParameterValue(fieldType, v);
	}

	public static Object toSqlParameterValue(int fieldType, String sv) {
		Object ret = null;
		if (sv != null) {
			switch (fieldType) {
			case Types.TINYINT:
				try {
					ret = new Byte(sv);
				} catch (Throwable e) {
				}
				break;
			case Types.SMALLINT:
				try {
					ret = new Short(sv);
				} catch (Throwable e) {
				}
				break;
			case Types.INTEGER:
				try {
					ret = new Integer(sv);
				} catch (Throwable e) {
				}
				break;
			case Types.REAL:
			case Types.FLOAT:
				try {
					ret = new Float(sv);
				} catch (Throwable e) {
				}
				break;
			case Types.BIGINT:
				try {
					ret = new Long(sv);
				} catch (Throwable e) {
				}
				break;
			case Types.DOUBLE:
				try {
					ret = new Double(sv);
				} catch (Throwable e) {
				}
				break;
			case Types.NUMERIC:
			case Types.DECIMAL:
				try {
					ret = new BigDecimal(sv);
				} catch (Throwable e) {
				}
				break;
			case Types.BIT:
				try {
					ret = new Boolean("1".equals(sv) || "true".equalsIgnoreCase(sv));
				} catch (Throwable e) {
				}
				break;
			case Types.DATE:
				try {
					ret = CalendarUtils.fromXmlDate(sv);
				} catch (Throwable e) {
				}
				break;
			case Types.TIME:
				try {
					ret = CalendarUtils.fromXmlTime(sv);
				} catch (Throwable e) {
				}
				break;
			case Types.TIMESTAMP:
				try {
					ret = CalendarUtils.fromXmlDateTime(sv);
				} catch (Throwable e) {
				}
				break;
			case Types.CLOB:
			case Types.BLOB:
			case Types.BINARY:
			case Types.VARBINARY:
			case Types.LONGVARBINARY:
			case Types.ARRAY:
			case Types.REF:
			case Types.DATALINK:
				if (StringUtils.hasText(sv)) {
					try {
						ret = new SqlLobValue(sv.getBytes());
					} catch (Throwable e) {
					}
				}
				break;
			default:
				ret = sv;
				break;
			}
		}
		return new SqlParameterValue(fieldType, ret);
	}

	public static Object toValue(int fieldType, String sv) {
		Object ret = null;
		if (sv != null) {
			switch (fieldType) {
			case Types.TINYINT:
				try {
					ret = new Byte(sv);
				} catch (Throwable e) {
				}
				break;
			case Types.SMALLINT:
				try {
					ret = new Short(sv);
				} catch (Throwable e) {
				}
				break;
			case Types.INTEGER:
				try {
					ret = new Integer(sv);
				} catch (Throwable e) {
				}
				break;
			case Types.REAL:
			case Types.FLOAT:
				try {
					ret = new Float(sv);
				} catch (Throwable e) {
				}
				break;
			case Types.BIGINT:
				try {
					ret = new Long(sv);
				} catch (Throwable e) {
				}
				break;
			case Types.DOUBLE:
				try {
					ret = new Double(sv);
				} catch (Throwable e) {
				}
				break;
			case Types.NUMERIC:
			case Types.DECIMAL:
				try {
					ret = new BigDecimal(sv);
				} catch (Throwable e) {
				}
				break;
			case Types.BIT:
				try {
					ret = new Boolean("1".equals(sv) || "true".equalsIgnoreCase(sv));
				} catch (Throwable e) {
				}
				break;
			case Types.DATE:
				try {
					ret = CalendarUtils.fromXmlDate(sv);
				} catch (Throwable e) {
				}
				break;
			case Types.TIME:
				try {
					ret = CalendarUtils.fromXmlTime(sv);
				} catch (Throwable e) {
				}
				break;
			case Types.TIMESTAMP:
				try {
					ret = CalendarUtils.fromXmlDateTime(sv);
				} catch (Throwable e) {
				}
				break;
			case Types.CLOB:
			case Types.BLOB:
			case Types.BINARY:
			case Types.VARBINARY:
			case Types.LONGVARBINARY:
			case Types.ARRAY:
			case Types.REF:
			case Types.DATALINK:
				if (StringUtils.hasText(sv)) {
					try {
						ret = new SqlLobValue(sv.getBytes());
					} catch (Throwable e) {
					}
				}
				break;
			default:
				ret = sv;
				break;
			}
		}
		return ret;
	}

	@SuppressWarnings("unchecked")
	public static String toStringValue(Object vo, int columnSQLType) {
		if (vo == null)
			return "";
		Class<?> voClazz = ClassUtils.getUserClass(vo);
		if (voClazz.isArray() || vo instanceof Object[] || vo instanceof Collection) {
			Assert.isTrue(columnSQLType == Types.VARCHAR, "类型错误!");
			StringBuilder vos = new StringBuilder("[");
			boolean append = false;
			Collection<Object> vob = (voClazz.isArray() || vo instanceof Object[]) ? CollectionUtils
					.arrayToList(voClazz) : (Collection<Object>) vo;
			for (Iterator<Object> vIter = vob.iterator(); vIter.hasNext();) {
				if (append)
					vos.append(",");
				append = true;
				vos.append(toStringValue(vIter.next(), Types.VARCHAR));
			}
			vos.append("]");
			return vos.toString();
		} else {
			String value = "";
			try {
				switch (columnSQLType) {
				case Types.BIT:
					if (vo instanceof Boolean) {
						Boolean vb = (Boolean) vo;
						value = vb ? "1" : "0";
					} else {
						throw new IllegalArgumentException("BIT只能由布尔型进行转换");
					}
					break;
				case Types.TINYINT:
				case Types.SMALLINT:
					value = (vo instanceof Boolean) ? (((Boolean) vo) ? "1" : "0") : vo.toString();
					Short.parseShort(value);
					break;
				case Types.INTEGER:
					value = (vo instanceof Boolean) ? (((Boolean) vo) ? "1" : "0") : vo.toString();
					Integer.parseInt(value);
					break;
				case Types.BIGINT:
					value = (vo instanceof Boolean) ? (((Boolean) vo) ? "1" : "0") : vo.toString();
					Long.parseLong(value);
					break;
				case Types.FLOAT:
				case Types.REAL:
					value = (vo instanceof Boolean) ? (((Boolean) vo) ? "1" : "0") : vo.toString();
					Float.parseFloat(value);
					break;
				case Types.NUMERIC:
				case Types.DECIMAL:
					value = vo.toString();
					BigDecimal bd = BigDecimal.valueOf(Double.parseDouble(value));
					value = bd.toString();
					break;
				case Types.DOUBLE:
					value = vo.toString();
					Double.parseDouble(value);
					break;
				case Types.CHAR:
				case Types.VARCHAR:
				case Types.LONGVARCHAR:
					value = vo.toString();
					break;
				case Types.DATE:
					if (vo instanceof Date) {
						value = CalendarUtils.toXmlDate((Date) vo);
					} else {
						throw new IllegalArgumentException(value);
					}
					break;
				case Types.TIMESTAMP:
					if (vo instanceof Date) {
						value = CalendarUtils.toXmlDateTime((Date) vo);
					} else {
						throw new IllegalArgumentException(value);
					}
					break;
				case Types.TIME:
					if (vo instanceof Date) {
						value = CalendarUtils.toXmlTime((Date) vo);
					} else {
						throw new IllegalArgumentException(value);
					}
					break;
				case Types.BOOLEAN:
					if (vo instanceof Boolean) {
						value = ((Boolean) vo).toString();
					} else if (vo instanceof String) {
						Boolean vob = Boolean.parseBoolean(vo.toString());
						value = vob.toString();
					} else {
						Boolean vob = Boolean.parseBoolean(vo.toString());
						value = vob.toString();
					}
					break;
				case Types.CLOB:
				case Types.BLOB:
				case Types.BINARY:
				case Types.VARBINARY:
				case Types.LONGVARBINARY:
				case Types.ARRAY:
				case Types.REF:
				case Types.DATALINK:
					if (vo instanceof String) {
						if (!HexUtils.isHex((String) vo)) {
							value = HexUtils.toHex(vo.toString().getBytes());
						} else {
							value = (String) vo;
						}
					} else if (vo instanceof byte[]) {
						value = HexUtils.toHex((byte[]) vo);
					} else {
						value = HexUtils.toHex(vo.toString().getBytes());
					}
					break;
				}
				return value;
			} catch (Throwable e) {
				throw new IllegalStateException(e.getMessage(), e);
			}
		}
	}

}
